from decimal import Decimal

from sqlalchemy import Integer, DECIMAL, CHAR
from datetime import date, datetime
from sqlmodel import SQLModel, Field, Column, String, Date
from entity.model.common_model import BaseCommonModel, UnsignedBigInt, UnsignedInt, UnsignedSmallInt


class CommunityDataCollection(BaseCommonModel, table=True):
    # __tablename__ = 'community_data_collection'
    __tablename__ = 'test_community_data_collection'

    id: int = Field(
        sa_column=Column(UnsignedBigInt, primary_key=True, nullable=False, comment="主键ID")
    )
    province: str = Field(
        sa_column=Column(String(150), comment="省份")
    )
    area: str = Field(
        sa_column=Column(String(150), comment="城市")
    )
    county: str = Field(
        sa_column=Column(String(150), comment="区域")
    )
    province_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="省份ID")
    )
    area_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="城市ID")
    )
    county_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="区域ID")
    )
    sector: str = Field(
        sa_column=Column(String(150), comment="板块")
    )
    community_name: str = Field(
        sa_column=Column(String(150), comment="小区名称")
    )
    community_id: str = Field(
        sa_column=Column(String(100), comment="采集到的小区id")
    )
    address: str = Field(
        sa_column=Column(String(255), comment="地址")
    )
    lon: Decimal = Field(
        sa_column=Column(DECIMAL(precision=20, scale=17), comment="坐标经度")
    )
    lat: Decimal = Field(
        sa_column=Column(DECIMAL(precision=20, scale=17), comment="坐标纬度")
    )
    construction_year: str = Field(
        sa_column=Column(String(50), comment="建筑年代")
    )
    residential_type: str = Field(
        sa_column=Column(String(50), comment="住宅类型:板楼、塔楼等")
    )
    property_fee: str = Field(
        sa_column=Column(String(100), comment="物业费")
    )
    property_company: str = Field(
        sa_column=Column(String(100), comment="物业公司")
    )
    developer: str = Field(
        sa_column=Column(String(120), comment="开发商")
    )
    building_id: str = Field(
        sa_column=Column(String(100), comment="采集到楼栋id")
    )
    regulatory_banks: str = Field(
        sa_column=Column(String(100), comment="监管银行")
    )
    regulatory_account: str = Field(
        sa_column=Column(String(100), comment="监管账号")
    )
    certificate_no: str = Field(
        sa_column=Column(String(100), comment="证书编号")
    )
    building_number: str = Field(
        sa_column=Column(String(100), comment="楼栋号")
    )
    unit_number: str = Field(
        sa_column=Column(String(50), comment="单元号")
    )
    room_id: str = Field(
        sa_column=Column(String(100), comment="采集到的小区房间id")
    )
    # room_no: str = Field(
    #     sa_column=Column(String(50), comment="房间号")
    # )
    room_number: str = Field(
        sa_column=Column(String(50), comment="房间号")
    )
    # room_number: int = Field(
    #     sa_column=Column(UnsignedSmallInt, comment="房间号in")
    # )
    floor: int = Field(
        sa_column=Column(Integer, comment="楼层")
    )
    building_area: Decimal = Field(
        sa_column=Column(DECIMAL(precision=11, scale=2), comment="建筑面积(㎡)")
    )
    inner_area: Decimal = Field(
        sa_column=Column(DECIMAL(precision=11, scale=2), comment="套内面积(㎡)")
    )
    public_area: Decimal = Field(
        sa_column=Column(DECIMAL(precision=11, scale=2), comment="公摊面积(㎡)")
    )
    purpose: str = Field(
        sa_column=Column(String(20), comment="房屋用途-住宅等")
    )
    sale_status: str = Field(
        sa_column=Column(String(20), comment="销售状态")
    )
    filing_time: date = Field(
        sa_column=Column(Date, comment="备案时间")
    )
    max_room_no: int = Field(
        sa_column=Column(UnsignedInt, comment="最大房号")
    )
    data_source: int = Field(
        sa_column=Column(UnsignedInt, default=1, comment="数据来源:1-采集,0-购买")
    )
    common_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="公共表id")
    )


class TblHouseManageLand(SQLModel, table=True):
    __tablename__ = 'tbl_house_manage_land'

    id: int = Field(
        sa_column=Column(UnsignedBigInt, primary_key=True, nullable=False, comment="主键ID")
    )
    hash_value: str = Field(sa_column=Column(CHAR(32), comment="hash值"))
    tenant_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="租户id")
    )
    province_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="省id")
    )
    area_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="城市id")
    )
    county_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="区域ID")
    )
    street_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="街道id")
    )
    land_name: str = Field(
        sa_column=Column(String(255), comment="名称")
    )
    notice_number_or_plot_code: str = Field(
        sa_column=Column(String(100), comment="编号")
    )
    land_address: str = Field(
        sa_column=Column(String(255), comment="位置")
    )
    plan_total_construction_area: Decimal = Field(
        sa_column=Column(DECIMAL(precision=19, scale=4), comment="规划总建面积(㎡)")
    )
    total_area: Decimal = Field(
        sa_column=Column(DECIMAL(precision=19, scale=4), comment="土地面积-总占地面积(㎡)")
    )
    land_property: str = Field(
        sa_column=Column(String(100), comment="宗地用途")
    )
    duration: int = Field(
        sa_column=Column(Integer, comment="年限")
    )
    plot_ratio: Decimal = Field(
        sa_column=Column(DECIMAL(precision=10, scale=4), comment="容积率")
    )
    base_price: Decimal = Field(
        sa_column=Column(DECIMAL(precision=19, scale=4), comment="出让起始价-出让底价")
    )
    announcement_date: date | None = Field(
        sa_column=Column(Date, comment="公告日期")
    )
    transaction_date: date | None = Field(
        sa_column=Column(Date, comment="成交日期")
    )
    winning_bidder: str = Field(
        sa_column=Column(String(255), comment="竞得方")
    )
    total_sale_price: Decimal = Field(
        sa_column=Column(DECIMAL(precision=19, scale=4), comment="成交总价(万元)")
    )
    floor_price: Decimal = Field(
        sa_column=Column(DECIMAL(precision=19, scale=4), comment="成交楼板价(元/㎡)")
    )
    transaction_status: int = Field(
        sa_column=Column(Integer, comment="交易状态")
    )
    sale_method: int = Field(
        sa_column=Column(Integer, comment="出让方式")
    )
    vr_cover: str = Field(
        sa_column=Column(String(255), comment="全景航拍(封面)")
    )
    vr_http_url: str = Field(
        sa_column=Column(String(255), comment="全景链接")
    )
    plan_condition: str = Field(
        sa_column=Column(String(255), comment="规划条件")
    )
    area_plan: str = Field(
        sa_column=Column(String(255), comment="片区规划")
    )